package be.kuleuven.peno3.mobiletoledo.server;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.QueryParam;

import com.google.gson.Gson;
import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.JsonObject;

@Path ("/CourseHandler")
public class CourseServer {

	protected DatabaseManager manager = DatabaseManager.getInstance();

	/*
	 * Method which returns all courses of a certain study programme.
	 */
	@GET
	@Path ("/getCourses")
	@Produces ("application/json")
	public String getCourses(@QueryParam("study_programme") String study_programme){
		
		String query = "SELECT * FROM Courses";
	    query += " WHERE study_programme= '"+study_programme+"'";
	    System.out.println(query);
		String result = queryForCourses(query);
		manager.disconnect();
		return result;
	}
	
	@GET
	@Path ("/getCourse")
	@Produces ("application/json")
	public String getCourse(@QueryParam("course_id") int course_id){
		
		String query = "SELECT * FROM Courses";
	    query += " WHERE id= '"+course_id+"'";
	    System.out.println(query);
		String result = queryForCourses(query);
		manager.disconnect();
		return result;
	}
	
	private String queryForCourses(String query) {
		JsonArray courses = new JsonArray();
		ResultSet rs = manager.query(query);
		Gson gson = new Gson();
		try {
			while(rs.next()) {
				 JsonObject course = (JsonObject) gson.toJsonTree(DatabaseManager.getColumnValues(rs));
				 JsonElement jsonElement = course.get("id");
				 System.out.println(jsonElement);
				 addTableRows(course,jsonElement,"Messages","messages");//messages of the course are added using the id
				 addTableRows(course,jsonElement,"Announcements","announcements"); //announcements are added using the id
				 
				 courses.add(course);
				}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		String asString = courses.toString();
		return asString;
	}
	/*
	 * private methode to add Rows of another table to a jsonObject
	 * @param JsonObject jsonObject
	 * @param JsonElement jsonElement
	 * @param String tableName
	 * @param String jsonElementName, this is the name of the jsonElement you add to your jsonObject
	 */
	private void addTableRows(JsonObject jsonObject,JsonElement jsonElement, String tableName, String jsonElementName){
		if (!jsonElement.isJsonNull()){
			 int id=jsonElement.getAsInt();
			// String query="SELECT * FROM '"+tableName+"' WHERE course_id='"+id+"'";//tableElements are added if they match the courseID
			 String query="SELECT * FROM "+tableName+" WHERE course_id='"+id+"'";//tableElements are added if they match the courseID
			 JsonArray result =querySimpleTable(query);
			 if(result.size() >0){
				 jsonObject.add(jsonElementName, result); //the whole result is added
			 }
			 else {
				 jsonObject.add(tableName,null);
			 }
			 
			 
		 }
	}
	
	@SuppressWarnings({ "unchecked", "static-access", "rawtypes" })
	private JsonArray querySimpleTable(String query) {
		System.out.println(query);
		Vector tableRows = new Vector();
		ResultSet rs = manager.query(query);
		Gson gson = new Gson();
		try {
			while(rs.next()) {
				tableRows.add(manager.getColumnValues(rs));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return (JsonArray) gson.toJsonTree(tableRows);
	}
}
